IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteAdvanceFundRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteAdvanceFundRequest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--DeleteAdvanceFundRequest 

CREATE PROCEDURE [dbo].[DeleteAdvanceFundRequest] 
(  
	@fwkDomainUserId	INT,
	@PropNumber	VARCHAR(50)
)  
AS 

SET NOCOUNT ON
BEGIN  

			DECLARE @AFRId INT

			SET @AFRId = (SELECT Id FROM dbo.AdvanceFundRequest WHERE PropNumber = @PropNumber AND SubmittedBy IS NULL)
			IF (ISNULL(@AFRId, 0) <> 0)
			BEGIN 

			DELETE FROM dbo.AdvanceFundRequestWorkFlowStatus
				WHERE AdvanceFundRequestId = @AFRId

			DELETE FROM dbo.AdvanceFundRequestUploadedDocument
				WHERE PropNumber = @PropNumber

			DELETE FROM dbo.AdvanceFundRequestEventHistory
				WHERE AdvanceFundRequestId = @AFRId

			DELETE FROM dbo.AdvanceFundRequestEmailNotification
				WHERE AdvanceFundRequestId = @AFRId

			DELETE FROM dbo.AdvanceFundRequestComments
				WHERE AdvanceFundRequestId = @AFRId

			DELETE FROM dbo.AdvanceFundRequest 						  
				WHERE PropNumber = @PropNumber AND SubmittedBy IS NULL	

			END 
			
END


GO